﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;

namespace WeiboTimer
{
    public class Sqlite
    {
        #region sqlite
        public bool CheckDbFile(string dbName)
        {
            if (File.Exists(dbName))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public void sqlite_test(string dbName)
        {
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder();
            conStr.DataSource = dbName;
            conn.ConnectionString = conStr.ToString();

            //open connetcion
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            string sql = string.Empty;
            cmd.Connection = conn;

            /*
            //create a table
            sql = "CREATE TABLE \"test3\" (\"day2\" INTEGER NOT NULL);";
            cmd.CommandText = sql;
            cmd.ExecuteScalar();
            */

            /*
            //insert data
            for (int i = 0; i <= 10; i++)
            {
                sql = "INSERT INTO test VALUES('cola','mypassword')";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
             */

            //select table
            sql = "SELECT * FROM test";
            cmd.CommandText = sql;
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader["day"].ToString() + "---" + reader["text"].ToString());
                }
            }

            conn.Close();
            //Console.Read();
        }
        public Dictionary<string, string> SearchCurrentWeibo(string dbName)
        {
            Dictionary<string, string> twitter_tbs = new Dictionary<string, string>();

            DateTime currentTime = DateTime.Now;
            string curYear = currentTime.Year.ToString();
            string curMonth = currentTime.Month.ToString();
            string curDay = currentTime.Day.ToString();
            string curHour = currentTime.Hour.ToString();
            string curMinute = currentTime.Minute.ToString();
            string curSecond = currentTime.Second.ToString();

            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder();
            conStr.DataSource = dbName;
            conn.ConnectionString = conStr.ToString();

            //open connetcion
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            string sql = string.Empty;
            cmd.Connection = conn;

            //select table
            //SELECT * FROM test001 WHERE Day = 2 AND Hour = 9 AND Minute = 8;
            //SELECT * FROM "test001" WHERE "Day" = 2 AND "Hour" = 9 AND "Minute" = 8;
            string tableName = "Month" + currentTime.ToString("MM");
            sql = "SELECT * FROM \"" + tableName + "\" WHERE \"Day\" = " + curDay + " AND \"Hour\" = " + curHour + " AND \"Minute\" = " + curMinute;
            cmd.CommandText = sql;
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (!String.IsNullOrEmpty(reader["Text"].ToString()))
                    {
                        twitter_tbs.Add("Forward", reader["Forward"].ToString());
                        twitter_tbs.Add("Text", reader["Text"].ToString());
                        twitter_tbs.Add("Picture", reader["Picture"].ToString());
                        break;
                    }
                }
            }

            conn.Close();
            return twitter_tbs;
        }
        #endregion
    }
}
